Skip Headers

Oracle® Database JDBC Developer's Guide and Reference 10g Release 2 (10.2) Part Number B14355-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 16 Working with LOBs and BFILEs

This chapter describes how to use Java Database Connectivity (JDBC) and the `oracle.sql.*` classes to access and manipulate large object (LOB) and binary file (BFILE) locators and data. This chapter contains the following sections:

- Oracle Extensions for LOBs and BFILEs

- Working with BLOBs and CLOBs

- Data Interface for LOBs

- Working With Temporary LOBs

- Using Open and Close With LOBs

- Working with BFILEs

Notes: In Oracle Database 10g, the Oracle JDBC drivers support the JDBC 3.0 `java.sql.Clob` and `java.sql.Blob` interfaces. Certain Oracle extensions made in `oracle.sql.CLOB` and `oracle.sql.BLOB` in earlier Oracle Database releases are no longer necessary and are deprecated. You should port your application to the standard JDBC 3.0 interface. Prior to Oracle Database 10g, the maximum size of a LOB was 232 bytes. This restriction is removed in Oracle Database 10g, and the maximum size is now limited to the size of available physical storage. The Java LOB application programming interface (API) has not changed.

## Oracle Extensions for LOBs and BFILEs

LOBs are stored in a way that optimizes space and provides efficient access. The JDBC drivers provide support for two types of LOB: binary large object (BLOB), which is used for unstructured binary data, and character large object (CLOB), which is used for character data. BLOB and CLOB data is accessed and referenced by using a locator that is stored in the database table and points to the BLOB or CLOB data, which is outside the table.

BFILEs are large binary data objects stored in operating system files outside of database tablespaces. These files use reference semantics. They can also be located on tertiary storage devices, such as hard disks, CD-ROMs, PhotoCDs, and DVDs. As with BLOBs and CLOBs, a BFILE is accessed and referenced by a locator which is stored in the database table and points to the BFILE data.

To work with LOB data, you must first obtain a LOB locator. Then you can read or write LOB data and perform data manipulation.

The JDBC drivers support the following `oracle.sql.*` classes for BLOBs, CLOBs, and BFILEs:

- `oracle.sql.BLOB`

- `oracle.sql.CLOB`

- `oracle.sql.BFILE`

The `oracle.sql.BLOB` and `oracle.sql.CLOB` classes implement the `java.sql.Blob` and `java.sql.Clob` interfaces, respectively. In contrast, `BFILE` is an Oracle extension, without a corresponding `java.sql` interface.

Instances of these classes contain only the locators for these data types, not the data. After accessing the locators, you must perform some additional steps to access the data.

Note: Your code cannot call the `BLOB`, `CLOB`, or `BFILE` constructors. To create a new LOB use the `createTemporary`, `getEmptyCLOB`, or `getEmptyBLOB` methods.

## Working with BLOBs and CLOBs

This section describes how to read and write data to and from BLOBs and CLOBs in Oracle Database, using LOB locators. This section covers the following topics:

- Getting and Passing BLOB and CLOB Locators

- Reading and Writing BLOB and CLOB Data

- Creating and Populating a BLOB or CLOB Column

- Accessing and Manipulating BLOB and CLOB Data

- Additional BLOB and CLOB Features

### Getting and Passing BLOB and CLOB Locators

Standard as well as Oracle-specific getter and setter methods are available for retrieving or passing LOB locators from or to the database. This section covers the following topics:

- Retrieving BLOB and CLOB Locators

- Passing BLOB and CLOB Locators

#### Retrieving BLOB and CLOB Locators

Given a standard JDBC result set or callable statement that includes BLOB or CLOB locators, you can access the locators by using standard getter methods. You can use the standard `getBlob` and `getClob` methods, which return `java.sql.Blob` and `Clob` objects, respectively.

Note: All the standard and Oracle-specific getter methods discussed here take either an `int` column index or a `String` column name as input.

If you retrieve or cast the result set or the callable statement to `OracleResultSet` or `OracleCallableStatement`, then you can use Oracle extensions, as follows:

- You can use `getBLOB` and `getCLOB`, which return `oracle.sql.BLOB` and `CLOB` objects, respectively.

- You can also use the `getOracleObject` method, which returns an `oracle.sql.Datum` object, and cast the output appropriately.

Example: Getting BLOB and CLOB Locators from a Result Set

Assume the database has a table called `lob_table` with a column for a BLOB locator, `blob_col`, and a column for a CLOB locator, `clob_col`. This example assumes that you have already created the `Statement` object, `stmt`.

First, select the LOB locators into a standard result set, then get the LOB data into appropriate Java classes:

    // Select LOB locator into standard result set.
    ResultSet rs =
       stmt.executeQuery ("SELECT blob_col, clob_col FROM lob_table");
    while (rs.next())
    {
       // Get LOB locators into Java wrapper classes.
       java.sql.Blob blob = (java.sql.Blob)rs.getObject(1);
       java.sql.Clob clob = (java.sql.Clob)rs.getObject(2);
       (...process...)
    }

The output is cast to `java.sql.Blob` and `java.sql.Clob`. As an alternative, you can cast the output to `oracle.sql.BLOB` and `oracle.sql.CLOB` to take advantage of extended functionality offered by the `oracle.sql.*` classes. For example, you can rewrite the preceding code to get the LOB locators as:

    // Get LOB locators into Java wrapper classes.
       oracle.sql.BLOB blob = (BLOB)rs.getObject(1);
       oracle.sql.CLOB clob = (CLOB)rs.getObject(2);
       (...process...)

Example: Getting a CLOB Locator from a Callable Statement

The callable statement methods for retrieving LOBs are identical to the result set methods.

For example, if you have an `OracleCallableStatement` instance, `ocs`, that calls a function `func` that has a CLOB output parameter, then set up the callable statement as in the following example.

This example registers `OracleTypes.CLOB` as the type code of the output parameter.

    OracleCallableStatement ocs =
       (OracleCallableStatement)conn.prepareCall("{? = call func()}");
    ocs.registerOutParameter(1, OracleTypes.CLOB);
    ocs.execute();
    oracle.sql.CLOB clob = ocs.getCLOB(1);

#### Passing BLOB and CLOB Locators

Given a standard JDBC prepared statement or callable statement, you can use standard setter methods to pass LOB locators. These methods are defined as follows:

    public void setBlob(int index, Blob value);
    public void setClob(int index, Clob value);

Note: If you pass a BLOB to a PL/SQL procedure, then the BLOB must be no bigger than 32K - 7. If you pass a BLOB that exceeds this limit, then you will receive a `SQLException`.

Given an Oracle-specific `OraclePreparedStatement` or `OracleCallableStatement`, then you can use Oracle extensions as follows:

- Use `setBLOB` and `setCLOB`, which take `oracle.sql.BLOB` and `CLOB` locators as input, respectively.

- Use the `setOracleObject` method, which simply specifies an `oracle.sql.Datum` input.

Example: Passing a BLOB Locator to a Prepared Statement

If you have an `OraclePreparedStatement` object `ops` and a BLOB named `my_blob`, then write the BLOB to the database as follows:

    OraclePreparedStatement ops = (OraclePreparedStatement)conn.prepareStatement
                                ("INSERT INTO blob_table VALUES(?)");
    ops.setBLOB(1, my_blob);
    ops.execute();

Example: Passing a CLOB Locator to a Callable Statement

If you have an `OracleCallableStatement` object `ocs` and a CLOB named `my_clob`, then input the CLOB to the stored procedure `proc` as follows:

    OracleCallableStatement ocs =
       (OracleCallableStatement)conn.prepareCall("{call proc(?))}");
    ocs.setClob(1, my_clob);
    ocs.execute();

### Reading and Writing BLOB and CLOB Data

Once you have a LOB locator, you can use JDBC methods to read and write the LOB data. LOB data is materialized as a Java array or stream. However, unlike most Java streams, a locator representing the LOB data is stored in the table. Thus, you can access the LOB data at any time during the life of the connection.

To read and write the LOB data, use the methods in the `oracle.sql.BLOB` or `oracle.sql.CLOB` class, as appropriate. These classes provide functionality such as reading from the LOB into an input stream, writing from an output stream into a LOB, determining the length of a LOB, and closing a LOB.

Notes: To write LOB data, the application must acquire a write lock on the LOB object. One way to accomplish this is through a `SELECT FOR UPDATE`. Also, you must disable auto-commit mode.

To read and write LOB data, you can use these methods:

- To read from a BLOB, use the `getBinaryStream` method of an `oracle.sql.BLOB` object to retrieve the entire `BLOB` as an input stream. This returns a `java.io.InputStream` object.

  As with any `InputStream` object, use one of the overloaded `read` methods to read the LOB data and use the `close` method when you finish.

- To write to a BLOB, use the `setBinaryStream` method of an `oracle.sql.BLOB` object to retrieve the BLOB as an output stream. This returns a `java.io.OutputStream` object to be written back to the BLOB.

  As with any `OutputStream` object, use one of the overloaded `write` methods to update the LOB data and use the `close` method when you finish.

- To read from a CLOB, use the `getAsciiStream` or `getCharacterStream` method of an `oracle.sql.CLOB` object to retrieve the entire CLOB as an input stream. The `getAsciiStream` method returns an ASCII input stream in a `java.io.InputStream` object. The `getCharacterStream` method returns a `Unicode` input stream in a `java.io.Reader` object.

  As with any `InputStream` or `Reader` object, use one of the overloaded `read` methods to read the LOB data and use the `close` method when you finish.

  You can also use the `getSubString` method of `oracle.sql.CLOB` object to retrieve a subset of the CLOB as a character string of type `java.lang.String`.

- To write to a CLOB, use the `setAsciiStream` or `setCharacterStream` method of an `oracle.sql.CLOB` object to retrieve the CLOB as an output stream to be written back to the CLOB. The `setAsciiStream` method returns an ASCII output stream in a `java.io.OutputStream` object. The `setCharacterStream` method returns a Unicode output stream in a `java.io.Writer` object.

  As with any `Stream` or `Writer` object, use one of the overloaded `write` methods to update the LOB data and use the `flush` and `close` methods when you finish.

  Notes: The stream write methods described in this section write directly to the database when you write to the output stream. You do not need to run an `UPDATE` to write the data. However, you need to call `close` or `flush` to ensure all changes are written. CLOBs and BLOBs are transaction controlled. After writing to either, you must commit the transaction for the changes to be permanent. BFILEs are not transaction controlled. Once you write to them the changes are permanent, even if the transaction is rolled back, unless the external file system does something else. When writing to or reading from a CLOB, the JDBC drivers perform all character set conversions for you.

Example: Reading BLOB Data

Use the `getBinaryStream` method of the `oracle.sql.BLOB` class to read BLOB data. The `getBinaryStream` method provides access to the BLOB data through a binary stream.

The following example uses the `getBinaryStream` method to read BLOB data through a byte stream and then reads the byte stream into a byte array, returning the number of bytes read, as well.

    // Read BLOB data from BLOB locator.
    InputStream byte_stream = my_blob.getBinaryStream(1L);
    byte [] byte_array = new byte [10];
    int bytes_read = byte_stream.read(byte_array);
    ...

Example: Reading CLOB Data

The following example uses the `getCharacterStream` method to read `CLOB` data into a Unicode character stream. It then reads the character stream into a character array, returning the number of characters read, as well.

    // Read CLOB data from CLOB locator into Reader char stream.
    Reader char_stream = my_clob.getCharacterStream(1L);
    char [] char_array = new char [10];
    int chars_read = char_stream.read (char_array, 0, 10);
    ...

The next example uses the `getAsciiStream` method of the `oracle.sql.CLOB` class to read CLOB data through an ASCII character stream. It then reads the ASCII stream into a byte array, returning the number of bytes read, as well.

    // Read CLOB data from CLOB locator into Input ASCII character stream
    Inputstream asciiChar_stream = my_clob.getAsciiStream(1L);
    byte[] asciiChar_array = new byte[10];
    int asciiChar_read = asciiChar_stream.read(asciiChar_array,0,10);

Example: Writing BLOB Data

Use the `setBinaryOutputStream` method of an `oracle.sql.BLOB` object to write BLOB data.

The following example reads a vector of data into a byte array, then uses the `setBinaryOutputStream` method to write an array of character data to a BLOB.

    java.io.OutputStream outstream;

    // read data into a byte array
    byte[] data = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9};

    // write the array of binary data to a BLOB
    outstream = ((BLOB)my_blob).setBinaryOutputStream(1L);
    outstream.write(data);
    ...

Example: Writing CLOB Data

Use the `setCharacterStream` method or the `setAsciiStream` method to write data to a CLOB. The `setCharacterStream` method returns a `Unicode` output stream. The `setAsciiStream` method returns an ASCII output stream.

The following example reads a vector of data into a character array, then uses the `setCharacterStream` method to write the array of character data to a CLOB.

    java.io.Writer writer;

    // read data into a character array
    char[] data = {'0','1','2','3','4','5','6','7','8','9'};

    // write the array of character data to a CLOB
    writer = ((CLOB)my_clob).setCharacterStream();
    writer.write(data);
    writer.flush();
    writer.close();
    ...

The next example reads a vector of data into a byte array, then uses the `setAsciiStream` method to write the array of ASCII data to a CLOB.

    java.io.OutputStream out;

    // read data into a byte array
    byte[] data = {'0','1','2','3','4','5','6','7','8','9'};

    // write the array of ascii data to a CLOB
    out = clob.setAsciiStream();
    out.write(data);
    out.flush();
    out.close();

### Creating and Populating a BLOB or CLOB Column

Create and populate a `BLOB` or `CLOB` column in a table by using SQL statements.

Note: You cannot construct a new `BLOB` or `CLOB` locator in your application with a Java `new` statement. You must create the locator through a SQL operation, and then select it into your application or with the `createTemporary` or `empty_lob` methods.

Create a `BLOB` or `CLOB` column in a table with the SQL `CREATE TABLE` statement, then populate the LOB. This includes creating the LOB entry in the table, obtaining the LOB locator, and then copying the data into the LOB.

Creating a BLOB or CLOB Column in a New Table

To create a `BLOB` or `CLOB` column in a new table, run the SQL `CREATE TABLE` statement. The following example code creates a `BLOB` column in a new table. This example assumes that you have already created your `Connection` object `conn` and `Statement` object `stmt`:

    String cmd = "CREATE TABLE my_blob_table (x VARCHAR2 (30), c BLOB)";
    stmt.execute (cmd);

In this example, the `VARCHAR2` column designates a row number, such as 1 or 2, and the `BLOB` column stores the locator of the BLOB data.

Populating a BLOB or CLOB Column in a New Table

This example demonstrates how to populate a `BLOB` or `CLOB` column by reading data from a stream. These steps assume that you have already created your `Connection` object `conn` and `Statement` object `stmt`. The table `my_blob_table` is the table that was created in the previous section.

The following example writes the `john.gif` file to a BLOB:

1. Begin by using SQL statements to create the BLOB entry in the table. Use the `empty_blob` function to create the BLOB locator.

       stmt.execute ("INSERT INTO my_blob_table VALUES ('row1', empty_blob())");

2. Get the BLOB locator from the table.

       BLOB blob;
       cmd = "SELECT * FROM my_blob_table WHERE X='row1' FOR UPDATE";
       ResultSet rset = stmt.executeQuery(cmd);
       rset.next();
       BLOB blob = ((OracleResultSet)rset).getBLOB(2);

   Note: You must disable auto-commit mode.

3. Declare a file handler for the `john.gif` file, then print the length of the file. This value will be used later to ensure that the entire file is read into the BLOB. Next, create a `FileInputStream` object to read the contents of the file, and an `OutputStream` object to retrieve the BLOB as a stream.

       File binaryFile = new File("john.gif");
       System.out.println("john.gif length = " + binaryFile.length());
       FileInputStream instream = new FileInputStream(binaryFile);
       OutputStream outstream = blob.setBinaryStream(1L);

4. Call `getBufferSize` to retrieve the ideal buffer size to use in writing to the BLOB, then create the `buffer` byte array.

       int size = blob.getBufferSize();
       byte[] buffer = new byte[size];
       int length = -1;

5. Use the `read` method to read the file to the byte array `buffer`, then use the `write` method to write it to the BLOB. When you finish, close the input and output streams and commit the changes.

       while ((length = instream.read(buffer)) != -1)
          outstream.write(buffer, 0, length);
       instream.close();
       outstream.close();
       conn.commit();

Once your data is in the BLOB or CLOB, you can manipulate the data.

### Accessing and Manipulating BLOB and CLOB Data

Once you have your BLOB or CLOB locator in a table, you can access and manipulate the data to which it points. To access and manipulate the data, you first must select their locators from a result set or from a callable statement.

After you select the locators, you can retrieve the BLOB or CLOB data. You will usually want to cast the result set to `OracleResultSet`, so that you can retrieve the data in `oracle.sql.*` format. After retrieving the BLOB or CLOB data, you can manipulate it however you want.

This example is a continuation of the example in the previous section. It uses the SQL `SELECT` statement to select the BLOB locator from the table `my_blob_table` into a result set. The result of the data manipulation is to print the length of the BLOB in bytes.

    // Select the blob - what we are really doing here
    // is getting the blob locator into a result set
    BLOB blob;
    cmd = "SELECT *  FROM my_blob_table";
    ResultSet rset = stmt.executeQuery (cmd);

    // Get the blob data - cast to OracleResult set to
    // retrieve the data in oracle.sql format
    String index = ((OracleResultSet)rset).getString(1);
    blob = ((OracleResultSet)rset).getBLOB(2);

    // get the length of the blob
    int length = blob.length();

    // print the length of the blob
    System.out.println("blob length" + length);

    // read the blob into a byte array
    // then print the blob from the array
    byte bytes[] = blob.getBytes(1, length);
    blob.printBytes(bytes, length);

### Additional BLOB and CLOB Features

In addition to what has already been discussed in this chapter, the `oracle.sql.BLOB` and `oracle.sql.CLOB` classes have a number of methods for further functionality.

Note: The `oracle.sql.CLOB` class supports all the character sets that the Oracle data server supports for CLOB types.

See Also: "Working With Temporary LOBs" and "Using Open and Close With LOBs"

Additional BLOB Methods

The `oracle.sql.BLOB` class includes the following methods:

- `close`

  Closes the BLOB associated with the locator.

- `freeTemporary`

  Frees the storage used by a temporary BLOB.

- `getBufferSize`

  Returns the ideal buffer size, according to calculations by the JDBC driver, to use in reading and writing BLOB data. This value is a multiple of the chunk size and is close to 32K.

- `getBytes`

  Reads from the BLOB data, starting at a specified point, into a supplied buffer. The method returns a `byte` array which holds the contents of the BLOB.

  Notes: In releases prior to Oracle Database 10g release 2 (10.2), the `getBytes` method when used on a `BLOB` column or output parameter incorrectly returns the bytes of the `LOB` locator, which is internal data and should not be used by applications. However, in Oracle Database 10g release 2 (10.2) the `getBytes` method returns a `byte` array that holds the contents of the `BLOB`. This method can give errors for `BLOB` data that exceed available memory or the size imposed by the Java language.

- `getChunkSize`

  Returns the Oracle chunking size, which can be specified by the database administrator when the LOB column is first created. This value, in Oracle blocks, determines the size of the chunks of data read or written by the LOB data layer in accessing or modifying the BLOB value. Part of each chunk stores system-related information, and the rest stores LOB data. Performance is enhanced if read and write requests use some multiple of the chunk size.

- `isOpen`

  Returns `true` if the BLOB was opened by calling the `open` method; otherwise, it returns `false`.

- `isTemporary`

  Returns true if the BLOB is a temporary BLOB.

- `length`

  Returns the length of the BLOB in bytes.

- `open`

  Opens the BLOB associated with the locator.

- `open(int)`

  Opens the BLOB associated with the locator in the mode specified by the argument.

- `position(byte[], long)`

  Determines the byte position in the BLOB where a given pattern begins.

- `setBinaryStream(long)`

  Returns the BLOB data for this `Blob` instance as a stream of bytes beginning at the position in the BLOB specified in the argument.

- `setBytes(long, byte[])`

  Writes BLOB data, starting at a specified point, from a supplied buffer.

- `truncate(long)`

  Trims the value of the BLOB to the length specified by the argument.

Additional CLOB Methods

The `oracle.sql.CLOB` class includes the following methods:

- `close`

  Closes the CLOB associated with the locator.

- `freeTemporary`

  Frees the storage used by a temporary CLOB.

- `getAsciiStream`

  Returns the CLOB value designated by the `Clob` object as a stream of ASCII bytes.

- `getAsciiStream(long)`

  Returns the CLOB value designated by the CLOB object as a stream of ASCII bytes, beginning at the position in the CLOB specified by the argument.

- `getBufferSize`

  Returns the ideal buffer size, according to calculations by the JDBC driver, to use in reading and writing CLOB data. This value is a multiple of the chunk size and is close to 32K.

- `getCharacterStream`

  Returns the CLOB data as a stream of Unicode characters.

- `getCharacterStream(long)`

  Returns the CLOB data as a stream of Unicode characters beginning at the position in the CLOB specified by the argument.

- `getChars`

  Retrieves characters from a specified point in the CLOB data into a character array.

- `getChunkSize`

  Returns the Oracle chunking size, which can be specified by the database administrator when the LOB column is first created. This value, in Oracle blocks, determines the size of the chunks of data read or written by the LOB data layer in accessing or modifying the CLOB value. Part of each chunk stores system-related information and the rest stores LOB data. Performance is enhanced if you make read and write requests using some multiple of the chunk size.

- `getSubString(long, int)`

  Retrieves a substring from a specified point in the CLOB data.

- `isOpen`

  Returns `true` if the CLOB was opened by calling the `open` method; otherwise, it returns `false`.

- `isTemporary`

  Returns `true` if and only if the CLOB is a temporary CLOB.

- `length`

  Returns the length of the CLOB in characters.

- `open`

  Opens the CLOB associated with the locator.

- `open(int)`

  Opens the CLOB associated with the locator in the mode specified by the argument.

- `position(String, long)`

  Determines the character position in the CLOB at which a given substring begins.

- `putChars(long, char[])`

  Writes characters from a character array to a specified point in the CLOB data.

- `setAsciiStream(long)`

  Returns a `java.io.OutputStream` object to write data to the CLOB as a stream. The data is written beginning at the position in the CLOB specified by the argument.

- `setCharacterStream(long)`

  Returns a `java.io.Writer` object to write data to the CLOB as a stream. The data is written beginning at the position in the CLOB specified by the argument.

- `setString(long pos, String str)`

  Writes a string to a specified point in the CLOB data.

- `truncate(long)`

  Trims the value of the CLOB to the length specified by the argument.

Creating Empty LOBs

Before writing data to an internal LOB, you must make sure the LOB column/attribute is not `null`. It must contain a locator. You can accomplish this by initializing the internal LOB as an empty LOB in an `INSERT` or `UPDATE` statement, using the `getEmpty` `XXX` methods defined in the `oracle.sql.BLOB` and `oracle.sql.CLOB` classes:

- `public static BLOB getEmptyBLOB() throws SQLException`

- `public static CLOB getEmptyCLOB() throws SQLException`

A JDBC driver creates an empty `LOB` instance without making database round trips. You can use empty LOBs in the following:

- `set` `XXX` ``methods of the `OraclePreparedStatement` class

- `update` `XXX` ``methods of updatable result sets

- attributes of `STRUCT` objects

- elements of `ARRAY` objects

  Note: Because a `getEmpty` `XXX` method creates a special marker that does not contain a locator, a JDBC application cannot read or write to it. The JDBC driver throws the exception `ORA-17098 Invalid empty LOB operation`, if a JDBC application attempts to read or write to an empty LOB before it is stored in the database.

## Data Interface for LOBs

The data interface for LOBs provides a streamlined mechanism for writing and reading the entire LOB contents. It is simpler to code and faster in many cases. It does not provide the random access capability or access beyond 2147483648 elements as do the standard `java.sql.Blob` and `java.sql.Clob` interfaces and the Oracle extensions, `oracle.sql.BLOB`, `oracle.sql.BFILE`, and `oracle.sql.CLOB`.

Input

In Oracle Database 10g release 2 (10.2), the `setBytes`, `setBinaryStream`, `setString`, `setCharacterStream`, and `setAsciiStream` methods of `PreparedStatement` are extended for `BLOB` and `CLOB` parameters.

For the JDBC Oracle Call Interface (OCI) and Thin drivers there is no limitation on the size of the `byte` array or `String` and no limit on the length specified for the stream functions except the limits imposed by the Java language, which is that array sizes are limited to positive Java `int` or 2147483648 elements.

For the server side internal driver there is currently a limitation of 4000 bytes for operations on SQL statements, such as an `INSERT` statement. The limitation does not apply for PL/SQL statements. There is a simple workaround for an `INSERT` statement, which is to wrap it in a PL/SQL block, as follows:

    BEGIN
     INSERT id, c INTO clob_tab VALUES(?,?);
    END;

You must bear in mind the following automatic switching of the input mode for large data:

- For SQL statements:

  - `setBytes` switches to `setBinaryStream` for data larger than 2000 bytes

  - `setString` switches to `setCharacterStream` for data larger than 32766 characters

- PL/SQL statements

  - `setBytes` switches to `setBinaryStream` for data larger than 2000 bytes and to `setBytesForBlob` for data larger that 32512 bytes

  - `setString` switches to `setStringForClob` for string data larger than 32512 bytes in the database character set or national character set depending on whether `setFormOfUse` has been used for `NCLOB` parameters

This will have impact on some programs, which formerly got `ORA-17157` errors for attempts to use `setString` for `String` values larger than 32766 characters. Now, depending on the type of the target parameter an error may occur while the application is executed or the operation may succeed.

Another impact is that the automatic switching may result in additional server side parsing to adapt to the change in the parameter type. This would result in a performance effect if the data sizes vary above and below the limit for repeated executions of the statement. Switching to the stream modes will effect batching as well.

Oracle Database 10g release 1 (10.1) has the `SetBigStringTryClob` connection property. Setting this property causes the standard `setString` method to switch to `setStringForClob` method for large data. This property is no longer used or needed. The `setBytesForBlob` and `setStringForClob` methods create temporary LOBs, which are automatically freed when the statement is executed or closed before execution.

However, when a PL/SQL procedure or function is embedded in a SQL statement, data less than 4 KB is bound as `String`, which is the standard. When data is greater than 4KB, the driver binds the data as a `String` as for any SQL statement. This will throw an error. The workaround is to use `setClob` or `setCharacterStream` instead of `setString` or `setStringForClob`. You can also create a callable statement.

Output

The `getBytes`, `getBinaryStream`, `getSting`, `getCharacterStream`, and `getAsciiStream` methods of `ResultSet` and `CallableStatement` are extended to work with `BLOB`, `CLOB`, and `BFILE` columns or `OUT` parameters. These methods will work for any LOB of length less than 2147483648. This operates entirely on the client-side and will work with any supported version of the database, that is, Oracle Database 8.1.7 and later.

`BLOB`, `BFILE`, or `CLOB` data can be read and written using the same streaming mechanism as for `LONG RAW` and `LONG` data. To read, use `defineColumnType(nn, Types.LONGVARBINARY)` or `defineColumnType(nn,Types.LONGVARCHAR)` on the column. This produces a direct stream on the data as if it were a `LONG RAW` or `LONG` column. This technique is limited to Oracle Database 10g release 1 (10.1) and later.

CallableSatement and IN OUT Parameter

It is a PL/SQL requirement that the Java types used as input and output for an IN OUT parameter must be the same. The automatic switching of types done by the extensions described in this chapter may cause problems with this.

Consider that you have an `IN OUT CLOB` parameter of a stored procedure and you wish to use `setString` for setting the value for this parameter. For any `IN` and `OUT` parameter, the binds must be of the same type. The automatic switching of the input mode will cause problems unless you are sure of the data sizes. For example, if it is known that neither the input nor output data will ever be larger than 32512 bytes, then you could use `setString` for the input parameter and register the `OUT` parameter as `Types.VARCHAR` and use `getString` for the output parameter.

A better solution is to change the stored procedure to have separate `IN` and `OUT` parameters. That is, if you have:

    CREATE PROCEDURE clob_proc( c IN OUT CLOB );

then, change it to:

    CREATE PROCEDURE clob_proc( c_in IN CLOB, c_out OUT CLOB );

Another workaround is to use a wrapper block to make the call. The `clob_proc` procedure can be wrapped with a Java string to use for the `prepareCall` statement, as follows:

    "DECLARE c_temp; BEGIN c_temp := ?; clob_proc( c_temp); ? := c_temp; END;"

In either case you may use `setString` on the first parameter and `registerOutParameter` with `Types.CLOB` on the second.

Size Limitations

Please be aware of the effect on the performance of the Java memory management system due to creation of very large `byte` array or `String`. Please read the information provided by your Java virtual machine (JVM) vendor about the impact of very large data elements on memory management, and consider using the stream interfaces instead.

## Working With Temporary LOBs

You can use temporary LOBs to store transient data. The data is stored in temporary table space rather than regular table space. You should free temporary LOBs after you no longer need them. If you do not, then the space the LOB consumes in temporary table space will not be reclaimed.

You can insert temporary LOBs into a table. When you do this, a permanent copy of the LOB is created and stored. Inserting a temporary LOB may be preferable for some situations. For example, if the LOB data is relatively small so that the overhead of copying the data is less than the cost of a database round trip to retrieve the empty locator. Remember that the data is initially stored in the temporary table space on the server and then moved into permanent storage.

You create a temporary LOB with the `static` method `createTemporary(Connection, boolean, int)`. This method is defined in both the `oracle.sql.BLOB` and `oracle.sql.CLOB` classes. You free a temporary LOB with the `freeTemporary` method.

    public static BLOB createTemporary(Connection conn, boolean isCached, int duration);
    public static CLOB createTemporary(Connection conn, boolean isCached, int duration);

The duration must be either `DURATION_SESSION` or `DURATION_CALL` as defined in the `oracle.sql.BLOB` or `oracle.sql.CLOB` class. In client applications, `DURATION_SESSION` is appropriate. In Java stored procedures, you can use either `DURATION_SESSION` or `DURATION_CALL`, which ever is appropriate.

You can test whether a LOB is temporary by calling the `isTemporary` method. If the LOB was created by calling the `createTemporary` method, then the `isTemporary` method returns `true`, else it returns `false`.

You can free a temporary LOB by calling the `freeTemporary` method. Free any temporary LOBs before ending the session or call. Otherwise, the storage used by the temporary LOB will not be reclaimed.

Notes: Failure to free a temporary LOB will result in the storage used by that LOB in the database being unavailable. Frequent failure to free temporary LOBs will result in filling up temporary table space with unavailable LOB storage. When fetching data from a `ReultSet` with columns that are temporary LOBs, use `getClob` or `getBlob` instead of `getString` or `getBytes`. Also invoke `freeTemporary` to free the temporary LOBs.

Creating Temporary NCLOBs

You create temporary national character large objects (NCLOBs) using a variant of the `createTemporary` method.

    CLOB.createTemporary (Connection conn, boolean cache, int duration, short form);

The `form` argument specifies whether the created LOB is a CLOB or an NCLOB. If `form` equals `oracle.jdbc.OraclePreparedStatement.FORM_NCHAR`, then the method creates an NCLOB. If `form` equals `oracle.jdbc.OraclePreparedStatement.FORM_CHAR`, then the method creates a CLOB.

## Using Open and Close With LOBs

You do not have to open and close your LOBs. You may choose to open and close them for performance reasons.

If you do not wrap LOB operations inside an Open/Close call operation, then each modification to the LOB will implicitly open and close the LOB, thereby firing any triggers on a domain index. Note that in this case, any domain indexes on the LOB will become updated as soon as LOB modifications are made. Therefore, domain LOB indexes are always valid and may be used at any time.

If you wrap your LOB operations inside the Open/Close call operation, then triggers will not be fired for each LOB modification. Instead, the trigger on domain indexes will be fired at the Close call. For example, you might design your application so that domain indexes are not be updated until you call the `close` method. However, this means that any domain indexes on the LOB will not be valid in-between the Open/Close calls.

You open a LOB by calling the `open` or `open(int)` method. You may then read and write the LOB without any triggers associated with that LOB firing. When you are done accessing the LOB, close the LOB by calling the `close` method. When you close the LOB, any triggers associated with the LOB will fire. You can see if a LOB is open or closed by calling the `isOpen` method. If you open the LOB by calling the `open(int)` method, the value of the argument must be either `MODE_READONLY` or `MODE_READWRITE`, as defined in the `oracle.sql.BLOB` and `oracle.sql.CLOB` classes. If you open the LOB with `MODE_READONLY`, any attempt to write to the LOB will result in a SQL exception.

Note: An error occurs if you commit the transaction before closing all LOBs that were opened by the transaction. The openness of the open LOBs is discarded, but the transaction is successfully committed. Hence, all the changes made to the LOB and non-LOB data in the transaction are committed, but the triggers for domain indexing are not fixed.

## Working with BFILEs

This section describes how to read and write data to and from BFILEs, using file locators. This section covers the following topics:

- Getting and Passing BFILE Locators

- Reading BFILE Data

- Creating and Populating a BFILE Column

- Accessing and Manipulating BFILE Data

- Additional BFILE Features

### Getting and Passing BFILE Locators

Getter and setter methods are available for retrieving or passing BFILE locators from or to the database.

Retrieving BFILE Locators

Given a standard JDBC result set or callable statement object that includes ``BFILE locators, you can access the locators by using the standard result set `getObject` method. This method returns an `oracle.sql.BFILE` object.

You can also access the locators by casting your result set to `OracleResultSet` or your callable statement to `OracleCallableStatement` and using the `getOracleObject` or `getBFILE` method.

Notes: In the `OracleResultSet` and `OracleCallableStatement` classes, `getBFILE` and `getBfile` both return `oracle.sql.BFILE`. There is no `java.sql` interface for BFILEs. If using `getObject` or `getOracleObject`, remember to cast the output, as necessary.

Example: Getting a BFILE locator from a Result Set

Assume that the database has a table called `bfile_table` with a single column for the BFILE locator `bfile_col`. This example assumes that you have already created your `Statement` object `stmt`.

Select the BFILE locator into a standard result set. If you cast the result set to `OracleResultSet`, then you can use `getBFILE` to get the BFILE locator, as follows:

    // Select the BFILE locator into a result set
    ResultSet rs = stmt.executeQuery("SELECT bfile_col FROM bfile_table");
    while (rs.next())
    {
       oracle.sql.BFILE my_bfile = ((OracleResultSet)rs).getBFILE(1);
    }

Note that as an alternative, you can use `getObject` to return the BFILE locator. In this case, because `getObject` returns a `java.lang.Object`, cast the results to `BFILE`. For example:

    oracle.sql.BFILE my_bfile = (BFILE)rs.getObject(1);

Example: Getting a BFILE Locator from a Callable Statement

Assume you have an `OracleCallableStatement` object `ocs` that calls a function `func` that has a `BFILE` output parameter. The following code example sets up the callable statement, registers the output parameter as `OracleTypes.BFILE`, runs the statement, and retrieves the BFILE locator:

    OracleCallableStatement ocs =
       (OracleCallableStatement)conn.prepareCall("{? = call func()}");
    ocs.registerOutParameter(1, OracleTypes.BFILE);
    ocs.execute();
    oracle.sql.BFILE bfile = ocs.getBFILE(1);

Passing BFILE Locators

To pass a BFILE locator to a prepared statement or callable statement, you can do one of the following:

- Use the standard `setObject` method.

- Cast the statement to `OraclePreparedStatement` or `OracleCallableStatement`, and use the `setOracleObject` or `setBFILE` method.

These methods take the parameter index and an `oracle.sql.BFILE` object as input.

Example: Passing a BFILE Locator to a Prepared Statement

Assume you want to insert a BFILE locator into a table, and you have an `OraclePreparedStatement` object `ops` to insert data into a table. The first column is a string, the second column is a BFILE, and you have a valid `oracle.sql.BFILE` object, `bfile`. Write the BFILE to the database, as follows:

    OraclePreparedStatement ops = (OraclePreparedStatement)conn.prepareStatement
                                ("INSERT INTO my_bfile_table VALUES (?,?)");
    ops.setString(1,"one");
    ops.setBFILE(2, bfile);
    ops.execute();

Example: Passing a BFILE Locator to a Callable Statement

Passing a BFILE locator to a callable statement is similar to passing it to a prepared statement. In this case, the BFILE locator is passed to the `myGetFileLength` procedure, which returns the BFILE length as a numeric value.

    OracleCallableStatement cstmt = (OracleCallableStatement)conn.prepareCall
                                   ("begin ? := myGetFileLength (?); end;");
    try
    {
       cstmt.registerOutParameter (1, Types.NUMERIC);
       cstmt.setBFILE (2, bfile);
       cstmt.execute ();
       return cstmt.getLong (1);
    }

### Reading BFILE Data

To read ``BFILE data, you must first get the BFILE locator. You can get the locator from either a callable statement or a result set. Once you obtain the locator, you can call a number of methods on the BFILE without opening it. For example, you can use the `oracle.sql.BFILE` methods `fileExists()` and `isFileOpen()` to determine whether the BFILE exists and if it is open. However, if you want to read and manipulate the data, then you must open and close the BFILE, as follows:

- Use the `openFile` method of the `oracle.sql.BFILE` class to open a BFILE.

- When you are done, use the `closeFile` method of the `BFILE` class.

BFILE data is through a Java stream. To read from a BFILE, use the `getBinaryStream` method of an `oracle.sql.BFILE` object to access the file as an input stream. This returns a `java.io.InputStream` object.

As with any `InputStream` object, use one of the overloaded `read` methods to read the file data and use the `close` method when you finish.

Notes: BFILEs are read-only. You cannot insert data or otherwise write to a BFILE. You can create a BFILE. However, you cannot create an operating system file that a BFILE would refer to. Those are created only externally.

Example: Reading BFILE Data

The following example uses the `getBinaryStream` method of an `oracle.sql.BFILE` object to read BFILE data into a byte stream and then read the byte stream into a byte array. The example assumes that the BFILE has already been opened.

    // Read BFILE data from a BFILE locator
    Inputstream in = bfile.getBinaryStream();
    byte[] byte_array = new byte{10};
    int byte_read = in.read(byte_array);

### Creating and Populating a BFILE Column

This section discusses how to create a `BFILE` column in a table with SQL operations and specify the location where the BFILE resides. The examples in this section assume that you have already created your `Connection` object `conn` and `Statement` object `stmt`.

Creating a BFILE Column in a New Table

To work with BFILE data, create a `BFILE` column in a table, and specify the location of the BFILE. To specify the location of the BFILE, use the SQL `CREATE DIRECTORY`...`AS` statement to specify an alias for the directory where the BFILE resides. In this example, the directory alias is `test_dir` and the BFILE resides in the `/home/work` directory.

    String cmd;
    cmd = "CREATE DIRECTORY test_dir AS '/home/work'";
    stmt.execute (cmd);

Use the SQL `CREATE` `TABLE` statement to create a table containing a `BFILE` column. In this example, the name of the table is `my_bfile_table`.

    // Create a table containing a BFILE field
    cmd = "CREATE TABLE my_bfile_table (x varchar2 (30), b bfile)";
    stmt.execute (cmd);

In this example, the `VARCHAR2` column designates a row number and the `BFILE` column stores the locator of the BFILE data.

Populating a BFILE Column

Use the SQL `INSERT INTO...VALUES` statement to populate the `VARCHAR2` and `BFILE` fields. The `BFILE` column is populated with the locator to the BFILE data. To populate the `BFILE` column, use the `bfilename` function to specify the directory alias and the name of the BFILE file.

    cmd ="INSERT INTO my_bfile_table VALUES ('one', bfilename(test_dir,
                                             'file1.data'))";
    stmt.execute (cmd);
    cmd ="INSERT INTO my_bfile_table VALUES ('two', bfilename(test_dir,
                                             'jdbcTest.data'))";
    stmt.execute (cmd);

In this example, the name of the directory alias is `test_dir`. The locator of the BFILE `file1.data` is loaded into the `BFILE` column on row `one`, and the locator of the `BFILE` `jdbcTest.data` is loaded into the `bfile` column on row `two`.

As an alternative, you may want to create the row for the row number and BFILE locator now, but wait until later to insert the locator. In this case, insert the row number into the table and `null` as a place holder for the BFILE locator.

    cmd ="INSERT INTO my_bfile_table VALUES ('three', null)";
    stmt.execute(cmd);

Here, `three` is inserted into the row number column and `null` is inserted as the place holder. Later in your program, insert the BFILE locator into the table by using a prepared statement.

First get a valid BFILE locator into the `bfile` object:

    rs = stmt.executeQuery("SELECT b FROM my_bfile_table WHERE x='two'");
    rs.next();
    oracle.sql.BFILE bfile = ((OracleResultSet)rs).getBFILE(1);

Then, create your prepared statement. Note that because this example uses the `setBFILE` method to identify the BFILE, the prepared statement must be cast to `OraclePreparedStatement`:

    OraclePreparedStatement ops = (OraclePreparedStatement)conn.prepareStatement
                          (UPDATE my_bfile_table SET b=? WHERE x = 'three');
    ops.setBFILE(1, bfile);
    ops.execute();

Now row `two` and row `three` contain the same BFILE.

Once you have the BFILE locators available in a table, you can access and manipulate the BFILE data.

### Accessing and Manipulating BFILE Data

Once you have the ``BFILE locator in a table, you can access and manipulate the data to which it points. To access and manipulate the data, you must first select its locator from a result set or a callable statement.

The following code continues the example from the preceding section, getting the locator of the BFILE from row `two` of a table into a result set. The result set is cast to `OracleResultSet` so that `oracle.sql.*` methods can be used on it. Several of the methods applied to the BFILE, such as `getDirAlias` and `getName`, do not require you to open the BFILE. Methods that manipulate the BFILE data, such as reading, getting the length, and displaying, do require you to open the BFILE.

When you finish manipulating the BFILE data, you must close the BFILE.

    // select the bfile locator
    cmd = "SELECT *  FROM my_bfile_table WHERE x = 'two'";
    rset = stmt.executeQuery (cmd);

    if (rset.next ())
        BFILE bfile = ((OracleResultSet)rset).getBFILE (2);

    // for these methods, you do not have to open the bfile
    println("getDirAlias() = " + bfile.getDirAlias());
    println("getName() = " + bfile.getName());
    println("fileExists() = " + bfile.fileExists());
    println("isFileOpen() = " + bfile.isFileOpen());

    // now open the bfile to get the data
    bfile.openFile();

    // get the BFILE data as a binary stream
    InputStream in = bfile.getBinaryStream();
    int length ;

    // read the bfile data in 6-byte chunks
    byte[] buf = new byte[6];

    while ((length = in.read(buf)) != -1)
    {
        // append and display the bfile data in 6-byte chunks
       StringBuffer sb = new StringBuffer(length);
       for (int i=0; i<length; i++)
          sb.append( (char)buf[i] );
       System.out.println(sb.toString());
    }

    // we are done working with the input stream. Close it.
    in.close();

    // we are done working with the BFILE. Close it.
    bfile.closeFile();

### Additional BFILE Features

In addition to the features already discussed in this chapter, the `oracle.sql.BFILE` class has a number of methods for further functionality, including the following:

- `closeFile`

  Closes the external file.

- `getBinaryStream`

  Returns the contents of the external file as a stream of bytes.

- `getBinaryStream(long)`

  Returns the contents of the external file as a stream of bytes beginning at the position in the external file specified by the argument.

- `getBytes`

  Reads from the external file, starting at a specified point, into a supplied buffer.

- `getName`

  Gets the name of the external file.

- `getDirAlias`

  Gets the directory alias of the external file.

- `isFileOpen`

  Determines whether the BFILE is open.

- `length`

  Returns the length of the BFILE in bytes.

- `openFile`

  Opens the external file for read-only access.

- `position`

  Determines the byte position at which the given byte pattern begins.